#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2019-08-02 09:39:09
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：

import openpyxl
import logging
import os
import io
import gc



class Parse_xlsx(object):
    """docstring for Parse_xlsx"""

    def __init__(self, filePath,):
        super(Parse_xlsx, self).__init__()
        self.filePath = filePath
        self.log= logging.getLogger("main.xlsx")
    def parse(self, beginRow=1):
        self.log.info("开始解析：{} ".format(self.filePath))
        with open(self.filePath,'rb+') as f:
            file=io.BytesIO(f.read())
            xlsx = openpyxl.load_workbook(file, data_only=True)
            # 打开excel
            sheetNames = xlsx.sheetnames
            # self.log.info("parse two sheet name={}".format(sheetNames))
            # 获取所有表名称
            sheetData = {}
            for sheetName in sheetNames:
                sheet = xlsx[sheetName]
                # 选择一个表
                rows = len(list(xlsx[sheetName].rows))  # 行
                if rows >= 1:  # 判断是否为空表
                    # 列名
                    columnNames = [openpyxl.utils.get_column_letter(x) for x in range(1, sheet.max_column + 1)]
                    # 工作薄数据
                    rowDatas = [ [sheet["{}{}".format(i,k)].value for i in columnNames] for k in range(beginRow, sheet.max_row + 1) if sheet["A{}".format(k)].value ]
                    # 存储到dict
                    sheetData.setdefault(sheetName, rowDatas)
            # self.log.info("parse data\n{}\n{}".format('-'*80,sheetData))
            xlsx.close()
            f.close()
            del xlsx,f
            gc.collect()
            return sheetData
    def modify(self):
        return openpyxl.load_workbook(self.filePath, data_only=True)
    def write(self, data, sheetName="Sheet", beginRow=1):
        # try:
            with open(self.filePath,'rb+') as f:
                file=io.BytesIO(f.read())
                xlsx = openpyxl.load_workbook(file)
                if sheetName not in xlsx.sheetnames: xlsx.create_sheet(sheetName)
                sheet=xlsx[sheetName]
                for rowNumber, rowData in enumerate(data):
                    for colNumber, value in enumerate(rowData):
                        sheet.cell(row=rowNumber + beginRow,
                                   column=colNumber + 1, value=value)
                xlsx.save(self.filePath)
                self.log.info("存储文件：{} 成功".format(self.filePath))
                xlsx.close()
                f.close()
                del xlsx,f
                gc.collect()
                return True
        # except Exception as e:
        #     self.log.error("异常，请检查，xlsx文件：{} 是否已打开，请先关闭文件 ".format(self.filePath))
# if __name__ == '__main__':
#     x = r"F:\pythonCode\S7\collect_config.xlsx"
#     a = Parse_xlsx(x)
#     xx = a.parse(1)
#     data = [
#         [1, 2, 3, 4, 5],
#         [11, 22, 33, 44, 55],
#         [66, 77, 88]
#     ]
#     a.write(data, "map", 2)
